﻿/**********************************************************************************
 * 作者：Darren Zhou
 * 小组：开发小组
 * 说明： SQLiteHelper数据库助手类
 *创建日期：2017-07-28 00:23:04
 * 版本号：V1.0.0
* *********************************************************************************/
using Common.DataBase.Interface;
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Runtime.CompilerServices;

namespace Common.DataBase.Implement {
  public class MySqlHelper : SQLHelper {

    private static log4net.ILog _logger = log4net.LogManager.GetLogger(typeof(MySqlHelper));
    
    private static MySqlHelper _instance = null;
    
    [MethodImplAttribute(MethodImplOptions.Synchronized)]
    private static MySqlHelper getInstance() {
      if (_instance == null) {
        _instance = new MySqlHelper();
      }
      return _instance;
    }
    /// <summary>
    /// 数据库类型
    /// </summary>
    public override DataBaseType DBType {
      get {
        return DataBaseType.MySQL;
      }
    }

    
    protected override void PrepareConnection() {
      connectString = ConfigurationManager.AppSettings["appDataBase"];
      if (string.IsNullOrWhiteSpace(connectString)) {
        connectString = ConfigurationManager.ConnectionStrings["appDataBase"].ConnectionString;
      }
      queryUserTableSQL = "";
      queryColumnsByTableNameSQL = "";
      connection = new MySql.Data.MySqlClient.MySqlConnection(connectString);
    }


    public MySqlHelper() : base(){

    }

    /// <summary>
    /// 构造SQL参数
    /// </summary>
    /// <param name="parameterName">参数名</param>
    /// <param name="value">参数值</param>
    /// <returns>参数对象</returns>
    public override DbParameter DbParam(string parameterName, object value) {
      return new MySql.Data.MySqlClient.MySqlParameter(parameterName,value);
    }

    /// <summary>
    /// 执行带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    //[MethodImplAttribute(MethodImplOptions.Synchronized)]
    public override int ExecuteNonQuery( string commandText, CommandType commandType, DbParameter[] parameters = null, bool isClose = true) {
      int effectiveRowCount = 0;
      try {

        //MySql.Data.MySqlClient.MySqlConnection _connection = (MySql.Data.MySqlClient.MySqlConnection)OpenConnection();
        using (MySql.Data.MySqlClient.MySqlConnection _connection = new MySql.Data.MySqlClient.MySqlConnection(connectString)) {
          _connection.Open();
          using (MySql.Data.MySqlClient.MySqlCommand _command = new MySql.Data.MySqlClient.MySqlCommand(commandText, _connection)) {
            _command.CommandTimeout = TIMEOUT;
            _command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0) {
              _command.Parameters.AddRange(parameters);
            }
            effectiveRowCount = _command.ExecuteNonQuery();
          }
          //_connection.Close();
          //_connection.Dispose();
        }
        if (parameters != null && parameters.Length > 0) {
          foreach (DbParameter parmeter in parameters) {
            commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value.ToString().Replace("\\", "\\\\")));
          }
        }        
        _logger.Debug(commandText);
      } catch(Exception exception) {
        if (parameters != null && parameters.Length > 0) {
          foreach (DbParameter parmeter in parameters) {
            commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value.ToString().Replace("\\", "\\\\")));
          }
        }
        _logger.Debug(commandText);
        _logger.Error( exception );
      }
      return effectiveRowCount;
    }



    
    /// <summary>
    /// 执行带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public override DataTable ExecuteQuery( string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true) {
      DataTable dataTable = new DataTable();
      try {
        using (MySql.Data.MySqlClient.MySqlConnection _connection = new MySql.Data.MySqlClient.MySqlConnection(connectString)) {
          _connection.Open();
          using (MySql.Data.MySqlClient.MySqlCommand _command = new MySql.Data.MySqlClient.MySqlCommand(commandText, _connection)) {
            _command.CommandTimeout = TIMEOUT;
            _command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0) {
              _command.Parameters.AddRange(parameters);
            }
            using (MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(_command)) {
              DataSet dataSet = new DataSet();
              dataAdapter.FillSchema(dataSet, SchemaType.Source);
              dataAdapter.Fill(dataSet);    //加载表数据
              dataTable = dataSet.Tables[0];
              dataAdapter.Dispose();
            }
          }
          _connection.Close();
          _connection.Dispose();
        }
      } catch(Exception exception) {
        foreach(DbParameter parmeter in parameters) {
          commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value));
        }
        _logger.Debug(commandText);
        _logger.Error( exception );
        dataTable = null;
      }
      return dataTable;
    }

  


    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    public override object ExecuteScalar(string commandText, DbParameter[] parameters, CommandType commandType , bool isClose = true) {
      object result = null;
      try {
        using (MySql.Data.MySqlClient.MySqlConnection _connection = new MySql.Data.MySqlClient.MySqlConnection(connectString)) {
          _connection.Open();
          using (MySql.Data.MySqlClient.MySqlCommand _command = new MySql.Data.MySqlClient.MySqlCommand(commandText, _connection)) {
            _command.CommandTimeout = TIMEOUT;
            _command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0) {
              _command.Parameters.AddRange(parameters);
            }
            result = _command.ExecuteScalar();
          }
          _connection.Close();
          _connection.Dispose();
        }
      } catch(Exception exception) {
        foreach(DbParameter parmeter in parameters) {
          commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value));
        }
        _logger.Debug(commandText);
        _logger.Error(exception);
      }
      return result;
    }



    #region 批量导入DataTable

    //public void BulkToDB(string tableName, DataTable dt)
    //{
    //    SQLiteConnection sqlConn = new SQLiteConnection(
    //        ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
    //    sqlite
    //    SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
    //    bulkCopy.DestinationTableName = tableName;
    //    bulkCopy.BatchSize = dt.Rows.Count;

    //    try
    //    {
    //        sqlConn.Open();
    //        if (dt != null && dt.Rows.Count != 0)
    //            bulkCopy.WriteToServer(dt);
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //    finally
    //    {
    //        sqlConn.Close();
    //        if (bulkCopy != null)
    //            bulkCopy.Close();
    //    }
    //}

    #endregion 批量导入DataTable
  }
}
